package pmbs;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Robert Yonas
 * Utilize PostGre API to connect and manage Database
 * for security reason, next development can utilize pool connection
 */
public class PostGre_func {
    private static PostGre_func singleton;
    public static Connection conn;
    
    public static synchronized PostGre_func getInstance() {
        //using singleton pattern where the methods are not static.
        if (singleton==null) {
            singleton = new PostGre_func();
        }
        return singleton;
    }
   
    public boolean connect() {
        // Find Postgre server connection
        
        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, null, ex);
            return false;
        }
        
        try {
            /**
             * result[0] = DatabaseName
             * result[1] = UserName
             * result[2] = Password
             * result[3] = Server
             * result[4] = Port
             */
            String[] config = Utility.getConfig();
            conn=DriverManager.getConnection("jdbc:postgresql://"+config[3]+":"+config[4]+"/"+config[0],config[1],config[2]);
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, null, ex);
            return false;
        }
        return true;
    }
    
    public void disconnect() {
        try {
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, null, ex);
        }
    }
    
    /**
     * These method is using query statement which should be called from store proc.
     */
    public ResultSet getCandidate(){
        String query = "";
        try {
            query = "Select * From \"Candidate\" ORDER BY \"CandidateName\" ASC";
            Statement myStatement = conn.createStatement();
            return myStatement.executeQuery(query);
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return null;
        }
    }
    
    public ResultSet getCompany(){
        String query = "";
        try {
            query = "Select * From \"Company\" ORDER BY \"CompanyName\" ASC";
            Statement myStatement = conn.createStatement();
            return myStatement.executeQuery(query);
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return null;
        }
    }
    
    public boolean saveCandidate(String name) {
        String query = "";
        try {
            Statement myStatement = conn.createStatement();
            query = "INSERT INTO \"Candidate\"(\"CandidateName\") VALUES ('"+name+"')";
            return myStatement.execute(query);
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return false;
        }
    }
    
    public boolean saveCompany (String name) {
        String query = "";
        try {
            Statement myStatement = conn.createStatement();
            query = "INSERT INTO \"Company\"(\"CompanyName\") VALUES ('"+name+"')";
            return myStatement.execute(query);
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return false;
        }        
    }
    
    public boolean saveInterview (String candidateID, String CompanyID, boolean isClosedSystem) {
        String query = "";
        try {
            Statement myStatement = conn.createStatement();
            query="Insert into \"Interview\" (\"CompanyID\",\"CandidateID\",\"IsClosedSystem\") Values ('"+
                    CompanyID+"','"+candidateID+"','"+isClosedSystem+"')";
            return myStatement.execute(query);            
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);            
            return false;
        }
    }
    
    public String getCandidateID (String candidateName) {
        String query = "";
        try {
            Statement myStatement = conn.createStatement();
            query="Select \"CandidateID\" from \"Candidate\" where \"CandidateName\"='" + candidateName + "'";
            ResultSet myRS = myStatement.executeQuery(query);
            if (myRS.next())
                return myRS.getString(1); // will return candidateID
            return "";
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);            
            return "";
        }
    }
    
    
    public String getCompanyID (String companyName) {
        String query = "";
        try {
            Statement myStatement = conn.createStatement();
            query="Select \"CompanyID\" from \"Company\" where \"CompanyName\"='" + companyName + "'";
            ResultSet myRS = myStatement.executeQuery(query);
            if (myRS.next())
                return myRS.getString(1); // will return companyID
            return "";
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return "";
        }
    }
    
    public ResultSet getCompanyFromInterview (String candidateName) {
        String query = "";
        String candidateID = getCandidateID(candidateName);
        try {
            Statement myStatement = conn.createStatement();
            query="Select \"CompanyName\",\"IsClosedSystem\",\"IsDone\" from \"Interview\" AS A,\"Company\" AS B "+
                    "where \"CandidateID\"='" + candidateID + "' AND A.\"CompanyID\" = B.\"CompanyID\"";
            ResultSet myRS = myStatement.executeQuery(query);
            return myRS; 
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return null;
        }
    }
    
    public boolean deleteCandidate (String candidateName) {
        String query = "";
        try {
            Statement myStatement = conn.createStatement();
            query="DELETE FROM \"Candidate\" WHERE \"CandidateName\"='"+candidateName+"'";
            return myStatement.execute(query);            
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);            
            return false;
        }        
    }
    
    public boolean deleteCompany (String companyName) {
        String query = "";
        try {
            Statement myStatement = conn.createStatement();
            query="DELETE FROM \"Company\" WHERE \"CompanyName\"='"+companyName+"'";
            return myStatement.execute(query);            
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);            
            return false;
        }                
    }
    
    public boolean deleteCompanyfromInterview (String candidateID, String companyID) {
        String query = "";
        try {
            Statement myStatement = conn.createStatement();
            query="DELETE FROM \"Interview\" WHERE \"CompanyID\"='"+companyID+"' AND \"CandidateID\"='" + candidateID + "'";
            return myStatement.execute(query);            
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);            
            return false;
        }                        
    }

    public ResultSet getCandidateFromInterview(String companyName) {
        //get data with their status for checkin feature
        String companyID = getCompanyID(companyName);
        String query = "";
        try {
            Statement myStatement = conn.createStatement();
            query="Select \"CandidateName\",\"IsClosedSystem\",\"IsDone\", \"IsOccupied\", \"IsPresent\" from \"Interview\" AS A, \"Candidate\" AS B"+
                    " where \"CompanyID\"='" + companyID + "' AND A.\"CandidateID\" = B.\"CandidateID\" ORDER BY \"CandidateName\" ASC";
            ResultSet myRS = myStatement.executeQuery(query);
            return myRS; 
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return null;
        }        
    }
    
    public byte getInterviewerCount (String companyName) {
        String query = "";
        try {
            String companyID = getCompanyID(companyName);
            Statement myStatement = conn.createStatement();
            query="SELECT Count(\"InterviewerName\") from \"Interviewer\" where \"CompanyID\"='" + companyID + "'";
            ResultSet myRS = myStatement.executeQuery(query);
            if (myRS.next())
                return myRS.getByte(1);
            return 0;
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return 0;
        }
    }
    
    public boolean isInterviewerOccupied (String interviewerName) {
        String query = "";
        try {
            Statement myStatement = conn.createStatement();
            query="SELECT \"IsOccupied\" from \"Interviewer\" where \"InterviewerName\"='" + interviewerName + "'";
            ResultSet myRS = myStatement.executeQuery(query);
            if (myRS.next())
                return myRS.getBoolean(1); 
            return false;
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return false;
        }        
    }
    
    public String getCandidateFromInterviewer (String interviewerName) {
        String query = "";
        try {
            Statement myStatement = conn.createStatement();
            query="SELECT \"CandidateName\" from \"Interviewer\" AS A, \"Interview\" AS B, \"Candidate\" AS C"+
                    " WHERE \"InterviewerName\"='" + interviewerName + 
                    "' AND A.\"InterviewID\"=B.\"InterviewID\" AND B.\"CandidateID\"=C.\"CandidateID\"";
            ResultSet myRS = myStatement.executeQuery(query);
            if (myRS.next())
                return myRS.getString(1); 
            return "";
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return "";
        }                
    }
    
    public boolean isCompanyPresent(String companyName) {
        String query = "";
        try {
            Statement myStatement = conn.createStatement();
            query="SELECT \"IsPresent\" from \"Company\" where \"CompanyName\"='" + companyName + "'";
            ResultSet myRS = myStatement.executeQuery(query);
            if (myRS.next())
                return myRS.getBoolean(1);
            return false;
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return false;
        }                
    }
    
    public boolean isCandidatePresent (String candidateName) {
        String query = "";
        try {
            Statement myStatement = conn.createStatement();
            query="SELECT \"IsPresent\" from \"Candidate\" where \"CandidateName\"='" + candidateName + "'";
            ResultSet myRS = myStatement.executeQuery(query);
            if (myRS.next())
                return myRS.getBoolean(1);
            return false;
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return false;
        }                        
    }
    
    public boolean isCandidateOccupied (String candidateName) {
                String query = "";
        try {
            Statement myStatement = conn.createStatement();
            query="SELECT \"IsOccupied\" from \"Candidate\" where \"CandidateName\"='" + candidateName + "'";
            ResultSet myRS = myStatement.executeQuery(query);
            if (myRS.next())
                return myRS.getBoolean(1);
            return false;
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return false;
        }                
    }
    
    public boolean saveInterviewer (String name, byte numberOfInterviewer, String Room) {
        String query = "";
        try {
            Statement myStatement = conn.createStatement();
            query = "UPDATE \"Company\" SET \"IsPresent\"=True WHERE \"CompanyName\"='"+name+"'";
            myStatement.execute(query);
            String companyID = getCompanyID(name);
            for (int i=1;i<=numberOfInterviewer;i++) {
                query = "INSERT INTO \"Interviewer\"(\"InterviewerName\",\"CompanyID\",\"Room\") "
                        + "VALUES ('" + name + i + "','"+ companyID +"','"+ Room +"')";
                myStatement.execute(query);
            }
            return true;
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return false;
        }        
    }
    
    public boolean setCandidatePresent (String name) {
        String query = "";
        try {
            Statement myStatement = conn.createStatement();
            query = "UPDATE \"Candidate\" SET \"IsPresent\"=True WHERE \"CandidateName\"='"+name +"'";
            return myStatement.execute(query);
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return false;
        }                
    }
    
    public boolean setCandidateOccupied (String name) {
        String query = "";
        try {
            Statement myStatement = conn.createStatement();
            query = "UPDATE \"Candidate\" SET \"IsOccupied\"=True WHERE \"CandidateName\"='"+ name+ "'";
            return myStatement.execute(query);
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return false;
        }                        
    }
    
    public boolean checkoutCandidate (String candidateName) {
        String query = "";
        try {
            Statement myStatement = conn.createStatement();
            // first check whether candidate is interviewing or not
            query = "SELECT \"InterviewerID\" FROM \"Candidate\" WHERE \"CandidateName\"='" + candidateName + "'";
            ResultSet mySet = myStatement.executeQuery(query);
            if (mySet.next()) {
                String interviewerID = mySet.getString(1); 
                if (interviewerID != null) {
                    query = "SELECT \"InterviewID\" FROM \"Interviewer\" WHERE \"InterviewerID\"='"+interviewerID+"'";
                    String interviewID="";
                    mySet = myStatement.executeQuery(query);
                    if (mySet.next())
                        interviewID = mySet.getString(1);
                    query = "UPDATE \"Interview\" SET \"IsDone\"=TRUE, \"Checkout\"=now() WHERE \"InterviewID\"='"+ interviewID+ "'";
                    myStatement.execute(query);
                    query = "UPDATE \"Interviewer\" SET \"IsOccupied\"=FALSE, \"InterviewID\"=null, \"StartingTime\"=now() WHERE \"InterviewerID\"='"+ interviewerID+ "'";
                    myStatement.execute(query);
                    query = "INSERT INTO \"Notification\"(\"Notification\") VALUES ('"+candidateName+" is now idle')";
                    myStatement.execute(query);
                } 
            }
            query = "UPDATE \"Candidate\" SET \"IsOccupied\"=FALSE, \"InterviewerID\"=null WHERE \"CandidateName\"='"+candidateName + "'";
            return myStatement.execute(query);                
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return false;
        }                                
    }
    
    public boolean checkinInterview (String candidateName, String interviewerName) {
        String query = "", companyID, candidateID, interviewerID="", interviewID ="";
        try {
            ResultSet mySet;
            Statement myStatement = conn.createStatement();
            // first, get candidateID
            candidateID = getCandidateID(candidateName);
            // second, get companyID
            companyID = getCompanyID(interviewerName.substring(0, interviewerName.length()-1));
            // third, get interviewID and set checkin time
            query = "SELECT \"InterviewID\" FROM \"Interview\" WHERE \"CandidateID\"='" + candidateID +"' AND \"CompanyID\"='" + companyID + "'";
            mySet = myStatement.executeQuery(query);      
            if (mySet.next()) {
                interviewID = mySet.getString(1);
            }
            
            query = "UPDATE \"Interview\" SET \"CheckIn\"=now() WHERE \"InterviewID\"='"+ interviewID + "'";
            myStatement.execute(query);
            // fourth, get interviewerID and set occupied, startingtime & interviewID
            query = "SELECT \"InterviewerID\" FROM \"Interviewer\" WHERE \"InterviewerName\"='" + interviewerName +"'";
            mySet = myStatement.executeQuery(query);      
            if (mySet.next()) {
                interviewerID = mySet.getString(1);
            }
            
            query = "UPDATE \"Interviewer\" SET \"IsOccupied\"=TRUE, \"StartingTime\"=now(), \"InterviewID\"='" + interviewID +
                    "' WHERE \"InterviewerName\"='" + interviewerName + "'";
            myStatement.execute(query);
            
            query = "INSERT INTO \"Notification\"(\"Notification\") VALUES ('"+candidateName+" Interviewed by "+ interviewerName +"')";
            myStatement.execute(query);

            //fifth, set candidate occupied, and interviewerID
            
            query = "UPDATE \"Candidate\" SET \"IsOccupied\"=TRUE, \"InterviewerID\"='"+interviewerID+
                    "' WHERE \"CandidateName\"='"+ candidateName + "'";
            return myStatement.execute(query);
       } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return false;
        }                                
    }
    
    public ResultSet showInterviewInfo(/*byte sort, */String room) {
        
        String query = "";
        try {
            query = "Select \"InterviewerName\", \"Room\", A.\"IsOccupied\", "
                    + "(Select \"CandidateName\" From \"Interview\" AS B, \"Candidate\" AS C "
                    + "WHERE A.\"InterviewID\"=B.\"InterviewID\" AND B.\"CandidateID\"=C.\"CandidateID\") "
                    + "AS \"CandidateName\", \"StartingTime\" From \"Interviewer\" AS A";
            if (room != null && !room.equalsIgnoreCase("[ALL]"))
                query += " WHERE A.\"Room\"='" + room +"'";
//            switch (sort) {
//                case 0 : //Sort By Location
//                    query += " ORDER BY \"Room\", \"InterviewerName\" ASC";
//                    break;
//                case 1 : //Sort By Status
//                    query += " ORDER BY \"IsOccupied\" DESC, \"InterviewerName\" ASC";
//                    break;
//                case 2 : //Sort By Time
//                    query += " ORDER BY \"IsOccupied\" DESC, \"StartingTime\", \"InterviewerName\" ASC";
//                    break;
//            }
            Statement myStatement = conn.createStatement();
            return myStatement.executeQuery(query);
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return null;
        }
    }

    public ResultSet getLocation() {
        String query = "";
        try {
            query = "Select \"Room\" From \"Interviewer\" GROUP BY \"Room\"";
            Statement myStatement = conn.createStatement();
            return myStatement.executeQuery(query);
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return null;
        }
    }
    
    public int getNotifMAX() {
        String query = "";
        try {
            Statement myStatement = conn.createStatement();
            query = "Select MAX(\"ID\") From \"Notification\"";
            ResultSet mySet = myStatement.executeQuery(query);
            if (mySet.next())
                return mySet.getInt(1);
            return 0;
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return 0;
        }        
    }
    
    public ResultSet getNotifRest(int MaxID) {
        String query = "";
        try {
            query = "Select \"Notification\" From \"Notification\" Where \"ID\">"+MaxID;
            Statement myStatement = conn.createStatement();
            return myStatement.executeQuery(query);
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return null;
        }        
    }
    
    public boolean toggleDone (String CandidateName, String CompanyName) {
        String candidateID = getCandidateID(CandidateName);
        String companyID = getCompanyID(CompanyName);
          String query = "";
        try {
            Statement myStatement = conn.createStatement();
            query = "UPDATE \"Interview\" SET \"IsDone\"=False WHERE \"CandidateID\"='"+candidateID +"' AND \"CompanyID\"='"+companyID+"'";
            return myStatement.execute(query);
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return false;
        }
    }
    
    public boolean updateInterviewerNumber (int currentCount, int newCount, String name, String Room) {
        String query = "";
        try {
            Statement myStatement = conn.createStatement();
            if (newCount>currentCount) {
                String companyID = getCompanyID(name);
                for (int i=currentCount+1;i<=newCount;i++) {
                    query = "INSERT INTO \"Interviewer\"(\"InterviewerName\",\"CompanyID\",\"Room\") "
                            + "VALUES ('" + name + i + "','"+ companyID +"','"+ Room +"')";
                    myStatement.execute(query);
                }
            }
            if (newCount<currentCount) {
                for (int i=currentCount;i>newCount;i--) {
                    query = "DELETE FROM \"Interviewer\" WHERE \"InterviewerName\" = '" + name + i + "'";
                    myStatement.execute(query);
                }
            }
            return true;
        } catch (SQLException ex) {
            Logger.getLogger("pmbs.CareerDay").log(Level.SEVERE, query, ex);
            return false;
        }
    }
}
